﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace UIT_CV_Management
{
    public partial class StatForm : Form
    {
        //SQL Begin
        String Query = "";
        String OrgBeginQuery = "SELECT Users.Username FROM Users\n" +
                        "LEFT JOIN LyLichKhoaHoc\n" +
                            "ON Users.MaLyLichKhoaHoc = LyLichKhoaHoc.MaLyLichKhoaHoc\n";
        String OrgLyLichQuery = "LEFT JOIN LyLichSoLuoc\n" +
                            "ON LyLichKhoaHoc.MaLyLichSoLuoc = LyLichSoLuoc.MaLyLichSoLuoc\n" +
                        "LEFT JOIN ThongTinPhu\n" +
                            "ON LyLichSoLuoc.MaThongTinPhu = ThongTinPhu.MaThongTinPhu\n" +
                        "LEFT JOIN NgheNghiep\n" +
                            "ON LyLichSoLuoc.MaNgheNghiep = NgheNghiep.MaNgheNghiep\n";
        String OrgQuaTrinhDaoTaoQuery = "LEFT JOIN QuaTrinhDaoTao\n" +
                            "ON LyLichKhoaHoc.MaQuaTrinhDaoTao = QuaTrinhDaoTao.MaQuaTrinhDaoTao\n";
        String OrgDaoTaoDaiHocQuery = "LEFT JOIN DaoTaoDaiHoc\n" +
                            "ON QuaTrinhDaoTao.MaDaoTaoDaiHoc = DaoTaoDaiHoc.MaDaoTaoDaiHoc\n" +
                        "LEFT JOIN ChiTietNoiHoc\n" +
                            "ON DaoTaoDaiHoc.MaNoiHoc = ChiTietNoiHoc.MaChiTietNoiHoc\n" +
                        "LEFT JOIN CheDoHoc\n" +
                            "ON ChiTietNoiHoc.MaCheDoHoc = CheDoHoc.MaCheDoHoc\n" +
                        "LEFT JOIN TieuLuan\n" +
                            "ON ChiTietNoiHoc.MaTieuLuan = TieuLuan.MaTieuLuan\n" +
                        "LEFT JOIN TotNghiep\n" +
                            "ON ChiTietNoiHoc.MaTotNghiep = TotNghiep.MaTotNghiep\n" +
                        "LEFT JOIN MonThiTotNghiep\n" +
                            "ON TotNghiep.MaMonThiTotNghiep = MonThiTotNghiep.MaMonThiTotNghiep\n";
        String OrgDaoTaoTrenDaiHocQuery = "LEFT JOIN DaoTaoTrenDaiHoc\n" +
                            "ON QuaTrinhDaoTao.MaDaoTaoTrenDaiHoc = DaoTaoTrenDaiHoc.MaDaoTaoTrenDaiHoc\n" +
                        "LEFT JOIN NghienCuu\n" +
                            "ON DaoTaoTrenDaiHoc.MaNghienCuu = NghienCuu.MaNghienCuu\n" +
                        "LEFT JOIN DiaDiemNghienCuu\n" +
                            "ON NghienCuu.MaDiaDiem = DiaDiemNghienCuu.MaDiaDiem\n" +
                        "LEFT JOIN ThucTap\n" +
                            "ON DaoTaoTrenDaiHoc.MaThucTap = ThucTap.MaThucTap\n" +
                        "LEFT JOIN DiaDiemThucTap\n" +
                            "ON ThucTap.MaDiaDiem = DiaDiemThucTap.MaDiaDiem\n";
        String OrgNgoaiNguHocHamQuery = "LEFT JOIN DaoTaoNgoaiNgu\n" +
                            "ON QuaTrinhDaoTao.MaDaoTaoNgoaiNgu = DaoTaoNgoaiNgu.MaDaoTaoNgoaiNgu\n" +
                        "LEFT JOIN DanhGiaTrinhDoNgoaiNgu\n" +
                            "ON DaoTaoNgoaiNgu.MaDanhGiaTrinhDo = DanhGiaTrinhDoNgoaiNgu.MaDanhGiaTrinhDo\n" +
                        "LEFT JOIN DaoTaoHocHam\n" +
                            "ON QuaTrinhDaoTao.MaDaoTaoHocHam = DaoTaoHocHam.MaDaoTaoHocHam\n" +
                        "LEFT JOIN HocHam\n" +
                            "ON DaoTaoHocHam.MaTenHocHam = HocHam.MaTenHocHam\n";
        String OrgHoatDongKHKTQuery = "LEFT JOIN HoatDongKhoaHocKyThuat\n" +
                            "ON LyLichKhoaHoc.MaHoatDongKHKT = HoatDongKhoaHocKyThuat.MaHoatDongKHKT\n" +
                        "LEFT JOIN QuaTrinhHoatDongKHKT\n" +
                            "ON HoatDongKhoaHocKyThuat.MaQuaTrinhHDKHKT = QuaTrinhHoatDongKHKT.MaQuaTrinhHDKHKT\n" +
                        "LEFT JOIN HoiNghiKHKT\n" +
                            "ON HoatDongKhoaHocKyThuat.MaHoiNghiKHKT = HoiNghiKHKT.MaHoiNghiKHKT\n" +
                        "LEFT JOIN BaoCaoCanBo\n" +
                            "ON HoiNghiKHKT.MaBaoCaoCanBo = BaoCaoCanBo.MaBaoCaoCanBo\n" +
                        "LEFT JOIN KhenThuong\n" +
                            "ON HoatDongKhoaHocKyThuat.MaKhenThuong = KhenThuong.MaKhenThuong\n";
        String OrgWHEREQuery = "WHERE ";

        public StatForm()
        {
            InitializeComponent();
            InitLanguage();
        }

                /// <summary>
        /// Initialize the language of form
        /// </summary>
        private void InitLanguage()
        {
            this.gbxDaiHoc.Text = Language._("STAT_FORM_DAO_TAO_DAI_HOC_GBX");
            this.lblThanhPho.Text = Language._("STAT_FORM_THANH_PHO_LBL");
            this.lblThoiGianTuDH.Text = Language._("STAT_FORM_THOI_GIAN_TU_DH_LBL");
            this.lblDenNgayDH.Text = Language._("STAT_FORM_DEN_NGAY_DH_LBL");
            this.chbxTieuLuan.Text = Language._("STAT_FORM_TIEU_LUAN_CHBX");
            this.chbxTotNghiep.Text = Language._("STAT_FORM_TOT_NGHIEP_CHBX");
            this.gbxTrenDaiHoc.Text = Language._("STAT_FORM_DAO_TAO_TREN_DAI_HOC_GBX");
            this.gbxThucTap.Text = Language._("STAT_FORM_THUC_TAP_GBX");
            this.gbxNghienCuu.Text = Language._("STAT_FORM_NGHIEN_CUU_GBX");
            this.lblQGThucTap.Text = Language._("STAT_FORM_QUOC_GIA_THUC_TAP_LBL");
            this.lblQGNghienCuu.Text = Language._("STAT_FORM_QUOC_GIA_NGHIEN_CUU_LBL");
            this.lblThoiGianTuTDH.Text = Language._("STAT_FORM_THOI_GIAN_TU_TDH_LBL");
            this.lblDenNgayTDH.Text = Language._("STAT_FORM_DEN_NGAY_TDH_LBL");
            this.gbxNN_HH.Text = Language._("STAT_FORM_NGOAI_NGU_HOC_HAM_GBX");
            this.chbxNgoaiNgu.Text = Language._("STAT_FORM_NGOAI_NGU_CHBX");
            this.chbxHocHam.Text = Language._("STAT_FORM_HOC_HAM_CHBX");
            this.gbxKHKT.Text = Language._("STAT_FORM_KHOA_HOC_KY_THUAT_GBX");
            this.chbxDeAn.Text = Language._("STAT_FORM_DE_AN_CHBX");
            this.chbxBaoCaoCanBo.Text = Language._("STAT_FORM_BAO_CAO_CAN_BO_CHBX");
            this.chbxKhenThuong.Text = Language._("STAT_FORM_KHEN_THUONG_CHBX");
            this.gbxKetQua.Text = Language._("STAT_FORM_KET_QUA_GBX");
            this.lblTitle.Text = Language._("STAT_FORM_TITLE_LBL");
            this.Text = Language._("STAT_FORM_TITLE");
        }

        private void Value_Changed(object sender, EventArgs e)
        {
            tlplResult.Visible = false;
            if (sender.GetType() == typeof(CheckBox))
            {
                CheckBox ctrl = (CheckBox)sender;
                if (ctrl.Name == "chbxTieuLuan" && ctrl.Checked)
                        chbxTotNghiep.Checked = false;
                if (ctrl.Name == "chbxTotNghiep" && ctrl.Checked)
                        chbxTieuLuan.Checked = false;
            }
            tmrQuery.Stop();
            tmrQuery.Start();

            pnlInfo.Visible = false;
        }

        private void gbxDaiHoc_Click(object sender, EventArgs e)
        {
            if (chbxDaiHoc.Checked)
            {
                foreach (Control ctrl in gbxDaiHoc.Controls)
                    ctrl.Enabled = false;
                chbxDaiHoc.Checked = false;
            }
            else
            {
                foreach (Control ctrl in gbxDaiHoc.Controls)
                    ctrl.Enabled = true;
                chbxDaiHoc.Checked = true;
            }
        }

        private void chbxDaiHoc_CheckedChanged(object sender, EventArgs e)
        {
            if (!chbxDaiHoc.Checked)
                foreach (Control ctrl in gbxDaiHoc.Controls)
                    ctrl.Enabled = false;
            else
                foreach (Control ctrl in gbxDaiHoc.Controls)
                    ctrl.Enabled = true;
        }

        private void gbxTrenDaiHoc_Click(object sender, EventArgs e)
        {
            if (chbxTrenDaiHoc.Checked)
            {
                foreach (Control ctrl in gbxTrenDaiHoc.Controls)
                    ctrl.Enabled = false;
                chbxTrenDaiHoc.Checked = false;
                chbxNghienCuu.Checked = false;
                chbxThucTap.Checked = false;
            }
            else
            {
                foreach (Control ctrl in gbxTrenDaiHoc.Controls)
                    ctrl.Enabled = true;
                chbxTrenDaiHoc.Checked = true;
                dtpStartTrenDaiHoc.TextChanged += new System.EventHandler(this.Value_Changed);
                dtpEndTrenDaiHoc.TextChanged += new System.EventHandler(this.Value_Changed);
            }
        }

        private void chbxTrenDaiHoc_CheckedChanged(object sender, EventArgs e)
        {
            if (!chbxTrenDaiHoc.Checked)
                foreach (Control ctrl in gbxTrenDaiHoc.Controls)
                    ctrl.Enabled = false;
            else
                foreach (Control ctrl in gbxTrenDaiHoc.Controls)
                    ctrl.Enabled = true;
        }

        private void gbxNN_HH_Click(object sender, EventArgs e)
        {
            if (chbxNN_HH.Checked)
            {
                foreach (Control ctrl in gbxNN_HH.Controls)
                    ctrl.Enabled = false;
                chbxNN_HH.Checked = false;
            }
            else
            {
                foreach (Control ctrl in gbxNN_HH.Controls)
                    ctrl.Enabled = true;
                chbxNN_HH.Checked = true;
            }
        }

        private void chbxNN_HH_CheckedChanged(object sender, EventArgs e)
        {
            if (!chbxNN_HH.Checked)
                foreach (Control ctrl in gbxNN_HH.Controls)
                    ctrl.Enabled = false;
            else
                foreach (Control ctrl in gbxNN_HH.Controls)
                    ctrl.Enabled = true;
        }

        private void gbxKHKT_Click(object sender, EventArgs e)
        {
            if (chbxKHKT.Checked)
            {
                foreach (Control ctrl in gbxKHKT.Controls)
                    ctrl.Enabled = false;
                chbxKHKT.Checked = false;
            }
            else
            {
                foreach (Control ctrl in gbxKHKT.Controls)
                    ctrl.Enabled = true;
                chbxKHKT.Checked = true;
            }
        }

        private void chbxKHKT_CheckedChanged(object sender, EventArgs e)
        {
            if (!chbxKHKT.Checked)
                foreach (Control ctrl in gbxKHKT.Controls)
                    ctrl.Enabled = false;
            else
                foreach (Control ctrl in gbxKHKT.Controls)
                    ctrl.Enabled = true;
        }

        private void gbxThucTap_Click(object sender, EventArgs e)
        {
            if (chbxThucTap.Checked)
            {
                chbxThucTap.Checked = false;
                foreach (Control ctrl in gbxThucTap.Controls)
                    ctrl.Enabled = false;
            }
            else
            {
                foreach (Control ctrl in gbxThucTap.Controls)
                    ctrl.Enabled = true;
                chbxThucTap.Checked = true;
                tbxCountryThucTap.TextChanged += new System.EventHandler(this.Value_Changed);
                foreach (Control ctrl in gbxNghienCuu.Controls)
                    ctrl.Enabled = false;
            }
        }

        private void chbxThucTap_CheckedChanged(object sender, EventArgs e)
        {
            if (chbxThucTap.Checked)
            {
                foreach (Control ctrl in gbxThucTap.Controls)
                    ctrl.Enabled = true;
                chbxNghienCuu.Checked = false;
                foreach (Control ctrl in gbxNghienCuu.Controls)
                    ctrl.Enabled = false;
            }
            else
                foreach (Control ctrl in gbxThucTap.Controls)
                    ctrl.Enabled = false;
        }

        private void gbxNghienCuu_Click(object sender, EventArgs e)
        {
            if (chbxNghienCuu.Checked)
            {
                chbxNghienCuu.Checked = false;
                foreach (Control ctrl in gbxNghienCuu.Controls)
                    ctrl.Enabled = false;
            }
            else
            {
                foreach (Control ctrl in gbxNghienCuu.Controls)
                    ctrl.Enabled = true;
                chbxNghienCuu.Checked = true;
                tbxCountryNghienCuu.TextChanged += new System.EventHandler(this.Value_Changed);
                foreach (Control ctrl in gbxThucTap.Controls)
                    ctrl.Enabled = false;
            }
        }

        private void chbxNghienCuu_CheckedChanged(object sender, EventArgs e)
        {
            if (chbxNghienCuu.Checked)
            {
                chbxThucTap.Checked = false;
                foreach (Control ctrl in gbxThucTap.Controls)
                    ctrl.Enabled = false;
                foreach (Control ctrl in gbxNghienCuu.Controls)
                    ctrl.Enabled = true;
            }
            else
                foreach (Control ctrl in gbxNghienCuu.Controls)
                    ctrl.Enabled = false;
        }

        private void chbxNgoaiNgu_CheckedChanged(object sender, EventArgs e)
        {
            if (chbxNgoaiNgu.Checked)
            {
                chbxHocHam.Checked = false;
            }
        }

        private void chbxHocHam_CheckedChanged(object sender, EventArgs e)
        {
            if (chbxHocHam.Checked)
            {
                chbxNgoaiNgu.Checked = false;
            }
        }

        private void tmrQuery_Tick(object sender, EventArgs e)
        {
            Query = OrgBeginQuery;

            Query += OrgLyLichQuery;
            Query += OrgQuaTrinhDaoTaoQuery;

            if (chbxDaiHoc.Checked)
                Query += OrgDaoTaoDaiHocQuery;

            if (chbxTrenDaiHoc.Checked)
                Query += OrgDaoTaoTrenDaiHocQuery;

            if (chbxNN_HH.Checked)
                Query += OrgNgoaiNguHocHamQuery;

            if (chbxKHKT.Checked)
                Query += OrgHoatDongKHKTQuery;

            Query += OrgWHEREQuery;

            if (chbxDaiHoc.Checked)
            {
                String _ThanhPho = tbxCity.Text;
                DateTime _NgayBatDau = dtpStartDaiHoc.Value;
                DateTime _NgayKetThuc = dtpEndDaiHoc.Value;
                String _TieuLuan = "";
                if(chbxTieuLuan.Checked)
                    _TieuLuan = "NULL";
                String _TotNghiep = "";
                if (chbxTotNghiep.Checked)
                    _TotNghiep = "NULL";
                DaoTaoDaiHoc(_ThanhPho, _NgayBatDau, _NgayKetThuc, _TieuLuan, _TotNghiep);
            }

            if (chbxTrenDaiHoc.Checked)
            { 
                String _QGThucTap = "";
                if (chbxThucTap.Checked)
                    _QGThucTap = tbxCountryThucTap.Text;
                String _QGNghienCuu = "";
                if (chbxNghienCuu.Checked)
                    _QGNghienCuu = tbxCountryNghienCuu.Text;
                DateTime _NgayBatDau = dtpStartTrenDaiHoc.Value;
                DateTime _NgayKetThuc = dtpEndTrenDaiHoc.Value;

                DaoTaoTrenDaiHoc(_QGThucTap, _QGNghienCuu, _NgayBatDau, _NgayKetThuc);

            }

            if (chbxNN_HH.Checked)
            {
                String _NgoaiNgu = "";
                if (chbxNgoaiNgu.Checked)
                    _NgoaiNgu = "NULL";
                String _HocHam = "";
                if (chbxHocHam.Checked)
                    _HocHam = "NULL";

                NgoaiNguVaHocHam(_NgoaiNgu, _HocHam);
            }

            if (chbxKHKT.Checked)
            {
                String _DeAn = "";
                if (chbxDeAn.Checked)
                    _DeAn = "NULL";
                String _BaoCaoCanBo = "";
                if (chbxBaoCaoCanBo.Checked)
                    _BaoCaoCanBo = "NULL";
                String _KhenThuong = "";
                if (chbxKhenThuong.Checked)
                    _KhenThuong = "NULL";

                HoatDongKHKT(_DeAn, _BaoCaoCanBo, _KhenThuong);
            }

            QueryAndGetResult();
            tmrQuery.Stop();
        }

        private void DaoTaoDaiHoc(String _ThanhPho, DateTime _NgayBatDau,
            DateTime _NgayKetThuc, String _TieuLuan, String _TotNghiep)
        {
            AddStatement("ChiTietNoiHoc.TenThanhPho", "=", _ThanhPho);
            String from = _NgayBatDau.ToString("yyyy-MM-dd");
            String to = _NgayKetThuc.ToString("yyyy-MM-dd");
            AddStatement("ChiTietNoiHoc.ThoiGianBatDau", ">=", from);
            AddStatement("ChiTietNoiHoc.ThoiGianKetThuc", "<=", to);
            AddStatement("ChiTietNoiHoc.MaTieuLuan", "<>", _TieuLuan);
            AddStatement("ChiTietNoiHoc.MaTotNghiep", "<>", _TotNghiep);
        }

        private void DaoTaoTrenDaiHoc(String _QGThucTap, String _QGNghienCuu, DateTime _NgayBatDau, DateTime _NgayKetThuc)
        {
            AddStatement("DiaDiemThucTap.TenNuoc", "=", _QGThucTap);
            AddStatement("DiaDiemNghienCuu.TenNuoc", "=", _QGNghienCuu);
            if (chbxThucTap.Checked)
            {
                String from = _NgayBatDau.ToString("yyyy-MM-dd");
                String to = _NgayKetThuc.ToString("yyyy-MM-dd");
                AddStatement("ThucTap.NgayBatDau", ">=", from);
                AddStatement("ThucTap.NgayKetThuc", "<=", to);
            }
            if (chbxNghienCuu.Checked)
            {
                String from = _NgayBatDau.ToString("yyyy-MM-dd");
                String to = _NgayKetThuc.ToString("yyyy-MM-dd");
                AddStatement("ThucTap.NgayBatDau", ">=", from);
                AddStatement("ThucTap.NgayKetThuc", "<=", to);
            }
        }

        private void NgoaiNguVaHocHam(String _NgoaiNgu, String _HocHam)
        {
            AddStatement("QuaTrinhDaoTao.MaDaoTaoNgoaiNgu", "<>", _NgoaiNgu);
            AddStatement("QuaTrinhDaoTao.MaDaoTaoHocHam", "<>", _HocHam);
        }

        private void HoatDongKHKT(String _DeAn, String _BaoCaoCanBo, String _KhenThuong)
        {
            AddStatement("HoatDongKhoaHocKyThuat.MaQuaTrinhHDKHKT", "<>", _DeAn);
            AddStatement("HoiNghiKHKT.MaBaoCaoCanBo", "<>", _BaoCaoCanBo);
            AddStatement("HoatDongKhoaHocKyThuat.MaKhenThuong", "<>", _KhenThuong);
        }

        private void AddStatement(String _Column, String _Comparison, String _Value)
        {
            if (_Value != "")
            {
                if (Query.Substring(Query.Length - 6) != "WHERE ")
                    if (Query.Substring(Query.Length - 6) != "AND ")
                        Query += "AND ";
                if (_Comparison == "LIKE")
                    _Value = "%" + _Value + "%";
                else
                    if (_Comparison != ">=" || _Comparison != "<=" || _Comparison != "<" || _Comparison != ">")
                    {
                        if (_Value != "NULL")
                            Query += _Column + " " + _Comparison + " N'" + _Value + "' ";
                        else if (_Comparison == "=")
                            Query += _Column + " " + _Comparison + " " + _Value + " ";
                        else if(_Comparison == "<>")
                            Query += _Column + " " + _Comparison + " '" + _Value + "' ";
                    }
                    else
                        Query += _Column + " " + _Comparison + " " + _Value + " ";
            }
        }

        private void QueryAndGetResult()
        {
            QueryResult result = Database.shareDatabase.Query(Query);
            if (result.ReturnCode == 200)
            {
                if (tlplResult.Controls.Count > 0) tlplResult.Controls.Clear();
                foreach (DataRow dr in result.Table.Rows)
                {
                    String Username = dr["Username"].ToString();
                    User usr = new User(Username);
                    ContactView ctvw = new ContactView();
                    LyLichSoLuoc llsl = usr.LyLichKhoaHoc.lyLichSoLuoc;
                    ctvw.UserName = llsl.hoTen;
                    //ctvw.Position = llsl.ngheNghiep.chucVu;
                    ctvw.Position = llsl.getNgheNghiep().chucVu;
                    ctvw.Address = llsl.noiSinh;
                    ctvw.Anchor = (AnchorStyles.Left | AnchorStyles.Right | AnchorStyles.Top);
                    ctvw.Click += new System.EventHandler(this.ContactView_Click);
                    foreach (Control ctrl in ctvw.Controls)
                        ctrl.Click += new System.EventHandler(this.ContactView_Click);
                    tlplResult.Controls.Add(ctvw);
                }
                tlplResult.Visible = true;
            }
            else
            {
                tmrQuery.Stop();
                //pbxLoading.Visible = false;
                tlplResult.Visible = false;
                lblInfo.Text = Language._("STAT_FORM_RESULT_NOTFOUND_MSG");
                pnlInfo.Visible = true;
                //wMessageBox.Show(this, Language._("STAT_FORM_RESULT_NOTFOUND_MSG"), Language._("STAT_FORM_RESULT_NOTFOUND_TITLE"), MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }

        private void ContactView_Click(object sender, EventArgs e)
        {
            Control ctvw = (Control)sender;
            if (sender.GetType() == typeof(ContactView))
            {
                if (ctvw.BackColor != Color.CornflowerBlue)
                {
                    ctvw.BackColor = Color.CornflowerBlue;
                    ctvw.ForeColor = Color.White;
                }
                else
                {
                    ctvw.BackColor = Color.White;
                    ctvw.ForeColor = Color.Black;
                }
            }
            else
            {
                if (ctvw.Parent.BackColor != Color.CornflowerBlue)
                {
                    ctvw.Parent.BackColor = Color.CornflowerBlue;
                    ctvw.Parent.ForeColor = Color.White;
                }
                else
                {
                    ctvw.Parent.BackColor = Color.White;
                    ctvw.Parent.ForeColor = Color.Black;
                }
            }
        }
    }
}
